Tuning Performance Of QuickBooks On A Network

According to Joanie, the Pro and Premier versions of the Sybase database engine for QuickBooks is the same engine. This database engine was “tuned” to a maximum of five concurrent users and an associated level of database transaction handling. We already know that a QuickBooks Pro or Premier data file is limited to allowing up to five users in multi-user mode in any given file, but this database engine tuning speaks to the total number of users and data files being served by the file server (and database transactions being performed), not to the number of users in a single data file. The file server, where the database engine resides, typically hosts the QuickBooks data files. When the engine in use on the server is Pro or Premier, that file server is expecting to serve up to five concurrent users and their associated data files.

In a large network, this becomes a problem because there could be a larger number of users (users with QuickBooks properly installed and licensed on their PC workstation), and all may be accessing files stored on a central file server. If the number of concurrent QuickBooks users exceeds five (regardless of whether they are all working on the same data file), the database manager will get bogged down and may start disconnecting users from their sessions. The problems may not be seen in some networks, where users are working largely in single-user mode. But where multi-user access is required on the network, the thresholds are easily reached.

The QuickBooks Enterprise edition of the database manager was tuned to handle a larger number of concurrent users and database transactions. We are not certain where the upward limitation really is with this database engine, as configured, but the thresholds are far higher than with the Pro/Premier engine.

When a network has a potential number of concurrent users greater than five, and where multiple data files are located on a central file server, my recommendation is to use the Enterprise edition database manager rather than using the engine that ships with Pro or Premier. The Enterprise database manager is compatible with Pro and Premier data files, and it significantly improves file server performance. For Pro and Premier users on larger networks, this means that you should plan the additional expense of a multi-user Enterprise license for the file server. The workstations can continue with their Pro/Premier versions.

In addition to the above recommendation, it’s worth noting that if the user launches QuickBooks via a shortcut to the data file rather than launching the program first and then “opening” the file, it could possibly crash the database engine or, at a minimum, limit the functionality of the application once it launches. For example, if there are multiple versions of QuickBooks installed on the machine, Windows will launch the most current version (which is not necessarily the version required to open the data file).

How Big is Your File?

Another important part of performance tuning is to consider the size of your QuickBooks data file.

To find out how big a data file is, open the company file and press F2 (or Ctrl+1). This displays the “Product Information” screen (Figure 1), and the file size is shown on the file information section of this screen.

In general, I recommend keeping data file sizes to within the following limits:

• For SimpleStart, Pro and Premier editions (through QuickBooks 2006), keep the file size to 100MB or less. For Enterprise Solutions, the file should not be much larger than 250MB.

• For version 2007 and above, Premier files should not exceed 200MB, and Enterprise files should not exceed 300MB.

These file size recommendations are general guidelines, based on several factors, but there is no specific maximum file size, and your system will not come to a crashing halt if you exceed these recommended sizes. As the data file grows, the overall performance of the software declines, and the risk of data corruption increases.

The rate of growth of a QuickBooks data file varies from company to company. No “average” or “typical” data file size exists since every business tracks information differently in QuickBooks. How quickly a data file grows depends on the number of transactions, the amount of information entered per transaction, and the number of “links” per transaction.

How to Reduce File Size

So if you’ve decided your file is too big, what should you do about it? Well, this is perhaps one of the hardest things QuickBooks consultants face. You essentially have two options:

• Use the Clean Up Company Data utility (Figure 2) to remove older data. This used to be called “condensing the data file.”

• Use the Clean Up Company Data utility to remove ALL data.

Understanding the Clean Up Data Utility

Condensing the file (now called Cleaning up the Data File), is not usually very productive. But depending on the data, it might moderately shrink the data file. During the “clean up” process, QuickBooks creates summary transactions (Journal Entries) for the transactions it deletes from your file. There is usually one summary transaction per month that represents the deleted transactions. For example, there will be one journal each month with the total of the Checks written for that month, along with the total amount in each account used on the deleted Checks. Note that you will not be able to edit any transactions created by the Clean Up Data Utility.

In all my years of consulting with QuickBooks, I’ve never found this “condensing” process to be very useful. The reason is that the Clean Up Data Utility is not quite as simple as it seems on the surface. After cleaning up a file, you may discover that QuickBooks retained numerous cleared Checks, paid Bills or closed Invoices. At first, it might seem that the clean up utility didn’t work correctly. However, the more likely explanation has to do with the transactions involved.

Use the following checklist to determine why QuickBooks retained a transaction while cleaning up the data file:

• Is the transaction uncleared, open or does it include an unbilled cost?
• Does the transaction include an Inventory Part Item? In many cases, inventory transactions will not be deleted.
• Is the transaction “connected to” or “linked to” a transaction matching one of the conditions above?
  • Example: If a Deposit transaction in the Checking account includes five Checks, but one of the Checks was for partial payment on an Invoice, the whole chain from the originating Invoice (Invoice, Payment and Deposit) will not be deleted during the clean up process. Also, since the other Checks on that same Deposit are associated with the open Invoice (because they are on the same Deposit), the rest of the five deposited Checks and their chains will not be removed. To fix this rather simple example, close the Invoice that still has an outstanding balance by issuing a Credit Memo (if appropriate). Then, apply the Credit Memo to the Invoice. Alternatively, you can change the original Invoice to match the amount received against it. However, it is usually better to create the Credit Memo since doing so will provide a better audit trail.

Consider the following before you use the Clean Up Data File Utility:

• If you have a large file, the utility usually runs for several hours.
• Quite often, the actual shrinkage in the file is small, especially if the client uses inventory or has numerous open or uncleared transactions.
• Investigating the cause for why transactions were not deleted is tedious and may take more time to troubleshoot than is economically viable.

Instead of Condensing, Use Remove All Transactions

If you want to remove prior period information from the data file, starting over with a new file is
a much cleaner way to accomplish the goal of shrinking the file. Of course, this requires more effort. But if your goal is to shrink the file, there is no free lunch. Starting over allows you to significantly streamline the data file and correct problems with the setup of the data file, if applicable. If you decide to start over with a new file, you can use the Clean Up Company Data utility and select Remove All Transactions. Note that if you have current-year payroll transactions, or other online transactions pending, QuickBooks won’t let you remove all the data. In this situation, call the QuickBooks payroll service for specific instructions.

The benefit of removing all the transactions rather than simply starting a new file is that it preserves your lists and other setup information such as custom reports, forms, online banking and payroll setup. Also, after you remove all the data, tools are available that allow you to copy transactions from your old data file into the new (blank) data file. One such utility is the Beginning Balance Transfer Utility by Karl Irvin (www.q2q.us). The Beginning Balance Transfer Utility allows you to take the ending balances in one QuickBooks file (as of any date) and make them the beginning balances in a new QuickBooks file. After transferring the beginning balances to the new file, you can transfer transactions from the beginning balance date to the current date using the Data Transfer Utility from the same vendor.

Take a Roundtrip to a Portable Company File

After removing the data, you might find that your data file is still much larger than it should be. To reduce the size, you can first save a “portable company file” and then restore that portable file back to a working data file. This “roundtrip” to a portable data file will strip all of the database indexing from the file and then rebuild the indexing for the reduced number of transactions.

Confirm Your Network Setup

In addition to upgrading to the Enterprise Database manager, and to shrinking the data file size, you should confirm that your network is set up properly for QuickBooks hosting.

QuickBooks 2006 and 2007 use a new database that requires a different multi-user installation and setup as compared to earlier versions. For QuickBooks 2005 and prior, you setup multi-user access to the QuickBooks data file by simply installing QuickBooks on each workstation in your local area network and placing the QuickBooks data file
(*.QBW) in a folder accessible to all of the workstations. This shared folder could be on one of the workstations (peer-to-peer) or on a server.

With QuickBooks 2007, there are two separate parts of the QuickBooks application (the Database Server and the Client Application) that can be installed separately or together. You do not have to install the QuickBooks client application on the server unless someone needs to use QuickBooks on the server computer to access QuickBooks data. Instead, you can simply install the QuickBooks Database Server Manager. However, I recommend that you install the QuickBooks Client application on the server anyway because it will give you convenient access to the client’s QuickBooks data when you are working onsite or when you use desktop sharing software or Terminal Services to access the client’s server.

Network Environment Description

You use QuickBooks in a network environment (i.e., with multiple users) if you:

• Have a client-server network where the server runs Microsoft Windows 2000 Server, Microsoft Windows 2003 Server or have a peer-to-peer network with all peers running Windows 2000 or above.

• Store QuickBooks data file(s) on the server in a shared folder. Alternatively, you can store the data in a shared folder on one of the workstations. Note that you should locate the QuickBooks data (and database server software) on the computer with the most available RAM and processing capability.

• Install QuickBooks on two or more workstations in order to open a QuickBooks data file on multiple workstations simultaneously.

Requirements

Verify that you have the following:

• Multiple QuickBooks licenses. You must have at least one license for each QuickBooks user in the network. You may install the Database Server Manager on one additional computer (the data server) above the number of license(s) you purchased without purchasing a separate license.

• QuickBooks Pro edition or above. QuickBooks SimpleStart does not support multi-user access.

• On the server computer (the computer on which the data files will be stored), you want Windows 2003 server, but the minimum requirement is Windows 2000.

• On each workstation, Windows XP Pro with Service Pack 2 (or higher) is strongly recommended. Windows 2000 or Windows XP Home edition can be configured to work, but are not recommended and may take extra configuration time and efforts.

Setting Up QuickBooks for Multi-User Access

The recommended local area network setup configuration for QuickBooks 2006-2007 is shown visually in Figure 4.

As you can see in the illustration, it is important to store your data files on the computer you designate as the “server” for your network. This server can actually be any one of the clients on your network, but the differentiating aspect of this machine is that it runs the QuickBooks Database manager, which takes control of all network accesses to files stored on that server. All other computers on the network need only to have the QuickBooks Client software installed, and thus will never become the “host” of any data files.

To provide access to all users on the network, you’ll need to put the QuickBooks data files
(*.QBW, *.ND, *.TLG, etc.) in a shared folder on your server computer.

Verifying Proper Multi-User Hosting Setup

As discussed above, there must be a “host” for the QuickBooks data file in order for QuickBooks to work in multi-user mode. In the recommended setup, you are setting up the server computer to “host” (or “serve”) data to other client machines on the network. This step is critical for the server to provide high-performance hosting of your data files. To verify that you have set up the server correctly, follow these steps:

• Step 1 — On the server, press F2 and check the bottom section of the Product Information screen. The “Local Server Information” will show your server’s computer name somewhere in the text string of the “Server Name” field (see Figure 5 above).
• Step 2 — On each client computer, open the data file located on the server. Press F2, and verify that all of the fields are empty in the “Local Server Information” section (see Figure 6 below). This ensures that you are using the server as the host of the data file, and that none of the clients are acting as the host of the data file (i.e., you have no “floating hosts”).
• Step 3 — If any of the client workstations report that it is the “server” of the data file (i.e., if the data in the lower portion of the screen shown in Figure 6 is NOT empty), you should stop the multi-user hosting function on that client. To do so, select the File menu, select Utilities, and then select Stop hosting multi-user access.

After you’ve considered all of these factors, you should be able to improve performance of your QuickBooks system to its maximum capability. If you’re still experiencing sluggish performance, you may have a “floating host” problem that I describe in The QuickBooks Consultant’s Reference Guide, which is available on my website (www.sleeter.com), but the steps above should have confirmed that you don’t have that problem.

So if you’re still having problems, consider upgrading your hardware. RAM is the first thing to consider, followed by faster processors, more hard drive space and faster hard drives. If you find other interesting performance enhancements, send me a note, and I’ll pass your advice along.